<?php
/**
 * handler for SQL queries.
 * $sql = "SELECT * FROM table";
 * $result = new DbResult($sql, $mysqli);
 *
 * @uses mysqli
 * @todo implement iterator http://fr.php.net/manual/fr/language.oop5.iterations.php
 * @link http://webappkit.net
 * @package webappkit
 * @subpackage db
 * @author J.Ducastel <nospam0@ducastel.name>
 * @license http://opensource.org/licenses/gpl-license.php GNU Public License
 */

class DbResult implements iDbResult {

    /**
     * factory
     * @param mysqli_result $result
     * @param string $sql SQL query
     * @return DbResult
     * /
    public static function fromResult(& $result, $sql) {
        return new DbResult($result, $sql);
    }/**/

    /**
     * constructor
     * @param string $sql
     * @param mysqli $mysqli
     */
    public function __construct($sql, & $mysqli) {
        $this->sql = $sql;
        $this->mysqli= &$mysqli;
    }

    /**
     * get back SQL
     *
     * @return string SQL query
     */
    public function getSql() {
        return $this->sql;
    }

    /**
     * sets mysqli reference
     * @param mysqli $mysqli
     * @return bool
     */
    public function setMysqli(& $mysqli) {
        $this->mysqli = &$mysqli;
        return true;
    }

    /**
     * fetch a line as associative array.
     * @access public
     * @return array
     */
    public function fetchAssoc() {
        if (!$result = $this->_getResult())
            return false;
        return $result->fetch_assoc();
    }/**/

    /**
     * fetch a line as object
     * @param string $class
     * @param array $params passed to class constructor
     * @return Object
     */
    public function fetchObject($class, $params=null) {
        if (!$result = $this->_getResult())
            return false;
        return $result->fetch_object($class, $params);
    }

    /**
     * fetch a line as csv
     * @return string
     */
    public function fetchCsv() {

    }

    /**
     * return lines count
     * if not buffered and lines remain, would be incorrect
     * @access public
     * @return int
     */
    function count() {
        $result = $this->_getResult();
        return $result->num_rows;
    }

    /**
     * move to line $n (starting 0)
     * @param int $line
     * @return bool
     */
    public function dataSeek($line) {
        $result = $this->_getResult();
        return $result->data_seek($line);
    }

    /**
     * get pages count having $lines lines per page, skip to page $page
     * @access public
     * @param int $lines lines per page
     * @param int $page go to page
     * @return int pages count
     * /
    function paginate($lines, $page=1) {
        $pages = ceil($this->count() / $lines); // echo $pages;

        return $pages;
    }/**/

    /**
     * return resource. execute query if necessary.
     * @return resource
     * /
    function & getResource() {
        if (!$this->resource) {
            // $this->resource=mysql_unbuffered_query($this->sql);
            $this->resource=mysql_query($this->sql);
            if (!$this->resource) {
                $this->error=mysql_error();
            }
        }
        return $this->resource;
    }/**/

    /**
     * @return sql error if occured
     */
    public function getError() {
        $this->_getResult();
        return $this->error;
    }

    /**
     * execute query
     * @return bool
     */
    public function execute() {
        $this->_getResult();
        return !$this->failed;
    }

    /**
     * tells if query failed
     * @return bool
     */
    public function failed() {
        $this->_getResult();
        return $this->failed;
    }

    /**
     * get insert id
     * @return string
     */
    public function insertId() {
        $this->_getResult();
        return $this->insert_id;
    }

    /**
     * force download as a CSV file
     * wont send anything if result is empty
     * @return int lines sent
     */
    public function downloadAsCsv($filename, $separator=';', $enclosure='"') {
        // check result
        if (!$this->count())
            return 0;
        // sending headers
        $this->_sendDownloadHeaders($filename.'.csv');
        // building header line
        $line = $this->fetchAssoc();
        echo $this->_buildCsvLine(array_keys($line), $separator, $enclosure);
        // first data line
        echo $this->_buildCsvLine($line, $separator, $enclosure);
        $count = 1;
        // remaining lines
        while ($line = $this->fetchAssoc()) {
            echo $this->_buildCsvLine($line, $separator, $enclosure);
            $count ++;
        }
        return $count;
    }

    /**
     * get mysqli_result
     * @return mysqli_result
     */
    protected function & _getResult() {
        if (!$this->result and !$this->failed) {
            $this->result = $this->mysqli->query($this->sql);
            $this->insert_id = $this->mysqli->insert_id;
            $this->failed = $this->result?false:true;
        }
        return $this->result;
    }

    /**
     * @var mysqli mysqli instance
     */
    protected $mysqli;

    /**
     * @var mysqli_result result instance
     */
    protected $result;

    /**
     * @var string sql query executed
     */
    protected $sql;

    /**
     * @var string generated id if relevant
     */
    protected $insert_id;

    /**
     * @var string SQL error if occured
     */
    // var $error;

    /**
     * @var bool true if query failed, false if query succeeded, null if not executed yet
     */
    var $failed;

    /**
     * @var bool if result is buffered
     */
    // var $buffered=false;

    /**
     * envoie les headers http pour telechargement
     * @access private
     * @param string $name nom de fichier
     * @param int $size taille en octets
     * @return bool
     */
    function _sendDownloadHeaders($name,$size=NULL) {
        if (isset($_SERVER['HTTP_USER_AGENT']) and $_SERVER['HTTP_USER_AGENT']=="MSIE") // internet explorer
            header("Content-type: application/force-download");
        else // un bon navigateur ;o)
            header("Content-type: application/octet-stream");
        if ($size)
            header("Content-Length: ".$size);
        header("Content-Disposition: attachment; filename=".$name);
        return true;
    }

    /**
     * builds a CSV line from an array
     * @param array $line data line
     * @param string $separator separator character
     * @param string $enclosure enclosure character
     * @return string CSV line with
     */
    function _buildCsvLine($line, $separator, $enclosure, $remove_linebreaks=true) {
        $csv = '';
        foreach ($line as $value) {
            // escaping enclosure
            $value = str_replace($enclosure, '\\'.$enclosure, $value);
            // escaping separator
            $value = str_replace($separator, '\\'.$separator, $value);
            // removing line breaks
            if ($remove_linebreaks) {
                $value = str_replace(chr(13),' ',$value);
                $value = str_replace(chr(10),' ',$value);
            }
            // building line
            $csv .= $enclosure
            .$value
            .$enclosure
            .$separator;
        }
        // wiping last separator, adding CR+LF
        return substr($csv,0,-1)
        .chr(13).chr(10);
        // escaping enclosure char
        $data = str_replace($enclosure, '\\'.$enclosure, $data);
        return $enclosure
        .join($enclosure.$separator.$enclosure, $data)
        .$enclosure
        .chr(13).chr(10);
    }
}
